package homework8_5;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SubjectDao implements SubjectDaoI {

	@Override
	public int add(Subject su) {
		// TODO 自动生成的方法存根

		int classhour = su.getClasshour();
		int gradeid = su.getGradeid();
		int act = su.getActive();
		String sn = su.getSubjectname();
		String sql = "insert into s_subject  (classhour ,gradeid,subjectname,active ) values(?,?,?,?,?)";
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, classhour);
			ps.setInt(2, gradeid);
			ps.setString(3, sn);
			ps.setInt(4, act);
			int r1 = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return 0;
	}

	@Override
	public int update(Subject su) {
		// TODO 自动生成的方法存根
		int id = su.getId();
		int classhour = su.getClasshour();
		int gradeid = su.getGradeid();
		int act = su.getActive();
		String sn = su.getSubjectname();
		String sql = "update s_subject set classhour = ?,active = ?,gradeid = ?,subjectname =? where id = ?";
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(5, id);
			ps.setInt(1, classhour);
			ps.setInt(3, gradeid);
			ps.setString(4, sn);
			ps.setInt(2, act);
			int r1 = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return 0;
	}

	@Override
	public int del(int id) {
		// TODO 自动生成的方法存根
		String sql = "delete from s_student where id = ?";
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setLong(1, id);
			int r1 = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return 0;
	}

	@Override
	public Subject get(int id) {
		// TODO 自动生成的方法存根
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Subject su = null;
		String sql = "select * from s_student where id=?";
		try {
			ps = con.prepareStatement(sql);
			ps.setLong(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				su = new Subject();
				su.setClasshour(rs.getInt("classhour"));
				su.setGradeid(rs.getInt("gradeid"));
				su.setSubjectname(rs.getString("subjectname"));
				su.setActive(rs.getInt("active"));
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return su;
	}

	@Override
	public List<Subject> searchAll() {
		// TODO 自动生成的方法存根
		String sql = "select * from s_subject order by id asc";

		List<Subject> datas = new ArrayList<>();
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Subject su = null;
		try {
			ps = con.prepareStatement(sql);

			rs = ps.executeQuery();
			while (rs.next()) {
				su = new Subject();
				su.setId(rs.getInt("id"));
				su.setClasshour(rs.getInt("classhour"));
				su.setGradeid(rs.getInt("gradeid"));
				su.setSubjectname(rs.getString("subjectname"));
				su.setActive(rs.getInt("active"));
				datas.add(su);
			}

		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return datas;
	}

	@Override
	public List<Subject> paging(int pageSize, int pageNo) {
		String sql = "select * from s_subject limit ?,?";
		List<Subject> datas = new ArrayList<>();
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, pageSize * (pageNo - 1));
			ps.setInt(2, pageSize);
			rs = ps.executeQuery();
			while (rs.next()) {
				Subject su = new Subject();
				su.setId(rs.getInt(1));
				su.setSubjectname(rs.getString(2));
				su.setClasshour(rs.getInt(3));
				su.setGradeid(rs.getInt(4));
				su.setActive(rs.getInt(5));
				datas.add(su);
			}

		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		return datas;

	}

}
